using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class HollywoodService
    {
        public static bool AddHollywood(Hollywood hollywood)
        {
            string sql =
                "INSERT Hollywood (Title, Point, Count, Location, VideoContent, Image, UploadDate, TeamID, XUHAO)" +
                "VALUES (@Title, @Point, @Count, @Location, @VideoContent, @Image, @UploadDate, @TeamID, @XUHAO)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", hollywood.Title),
				new SqlParameter("@Point", hollywood.Point),
				new SqlParameter("@Count", hollywood.Count),
				new SqlParameter("@Location", hollywood.Location),
				new SqlParameter("@VideoContent", hollywood.VideoContent),
                new SqlParameter("@Image", hollywood.Image),
                new SqlParameter("@UploadDate", hollywood.UploadDate),
				new SqlParameter("@TeamID", hollywood.TeamID),
				new SqlParameter("@XUHAO", hollywood.XUHAO)

			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static int AddHollywoodReturnID(Hollywood hollywood)
        {
            string sql =
                "INSERT Hollywood (Title, Point, Count, Location, VideoContent, Image, UploadDate, TeamID, XUHAO)" +
                "VALUES (@Title, @Point, @Count, @Location, @VideoContent, @Image, @UploadDate, @TeamID, @XUHAO)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", hollywood.Title),
				new SqlParameter("@Point", hollywood.Point),
				new SqlParameter("@Count", hollywood.Count),
				new SqlParameter("@Location", hollywood.Location),
				new SqlParameter("@VideoContent", hollywood.VideoContent),
                new SqlParameter("@Image", hollywood.Image),
                new SqlParameter("@UploadDate", hollywood.UploadDate),
				new SqlParameter("@TeamID", hollywood.TeamID),
				new SqlParameter("@XUHAO", hollywood.XUHAO)
			};

            int i = Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sql, para));
            return i;
        }


        public static bool DeleteHollywoodByID(int iD)
        {
            string sql = "DELETE Hollywood WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyHollywood(Hollywood hollywood)
        {
            string sql =
                "UPDATE Hollywood " +
                "SET " +
                    "Title = @Title, " +
                    "Point = @Point, " +
                    "Count = @Count, " +
                    "Location = @Location, " +
                    "VideoContent = @VideoContent, " +
                    "Image = @Image, " +
                    "UploadDate = @UploadDate, " +
                    "TeamID = @TeamID, " +
                    "XUHAO = @XUHAO " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", hollywood.ID),
				new SqlParameter("@Title", hollywood.Title),
				new SqlParameter("@Point", hollywood.Point),
				new SqlParameter("@Count", hollywood.Count),
				new SqlParameter("@Location", hollywood.Location),
				new SqlParameter("@VideoContent", hollywood.VideoContent),
                new SqlParameter("@Image", hollywood.Image),
                new SqlParameter("@UploadDate", hollywood.UploadDate),
				new SqlParameter("@TeamID", hollywood.TeamID),
				new SqlParameter("@XUHAO", hollywood.XUHAO)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static bool ModifyHollywoodCount(int id, int count)
        {
            string sql = "UPDATE Hollywood SET Count = @Count WHERE ID = @ID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", id),
				new SqlParameter("@Count", count)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<Hollywood> GetAllHollywoods()
        {
            string sqlAll = "SELECT * FROM Hollywood";
            return GetHollywoodsBySql(sqlAll);
        }

        public static IList<Hollywood> GetHollywoodsLast()
        {
            string sqlAll = "SELECT top 9 * FROM Hollywood order by UploadDate desc";
            return GetHollywoodsBySql(sqlAll);
        }

        public static IList<Hollywood> GetHollywoodsByTeamID(int teamID)
        {
            string sqlAll = "SELECT top 9 * FROM Hollywood where TeamID=@TeamID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@TeamID", teamID)
			};
            return GetHollywoodsBySql(sqlAll, para);
        }

        public static Hollywood GetHollywoodByID(int iD)
        {
            string sql = "SELECT * FROM Hollywood WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            Hollywood hollywood = new Hollywood();

            foreach (DataRow dr in temp.Rows)
            {
                hollywood.ID = (int)dr["ID"];
                hollywood.Title = (string)dr["Title"];
                hollywood.Point = Convert.ToSingle(dr["Point"]);
                hollywood.Count = (int)dr["Count"];
                hollywood.Location = (string)dr["Location"];
                hollywood.VideoContent = (string)dr["VideoContent"];
                hollywood.Image = (string)dr["Image"];
                hollywood.UploadDate = (DateTime)dr["UploadDate"];
                hollywood.TeamID = (int)dr["TeamID"];
                hollywood.XUHAO = (int)dr["XUHAO"];

            }
            return hollywood;

        }


        private static IList<Hollywood> GetHollywoodsBySql(string safeSql)
        {
            List<Hollywood> list = new List<Hollywood>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    Hollywood hollywood = new Hollywood();

                    hollywood.ID = (int)dr["ID"];
                    hollywood.Title = (string)dr["Title"];
                    hollywood.Point = Convert.ToSingle(dr["Point"]);
                    hollywood.Count = (int)dr["Count"];
                    hollywood.Location = (string)dr["Location"];
                    hollywood.VideoContent = (string)dr["VideoContent"];
                    hollywood.Image = (string)dr["Image"];
                    hollywood.UploadDate = (DateTime)dr["UploadDate"];
                    hollywood.TeamID = (int)dr["TeamID"];
                    hollywood.XUHAO = (int)dr["XUHAO"];

                    list.Add(hollywood);
                }
            }
            return list;
        }

        private static IList<Hollywood> GetHollywoodsBySql(string safeSql, params SqlParameter[] para)
        {
            List<Hollywood> list = new List<Hollywood>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    Hollywood hollywood = new Hollywood();

                    hollywood.ID = (int)dr["ID"];
                    hollywood.Title = (string)dr["Title"];
                    hollywood.Point = Convert.ToSingle(dr["Point"]);
                    hollywood.Count = (int)dr["Count"];
                    hollywood.Location = (string)dr["Location"];
                    hollywood.VideoContent = (string)dr["VideoContent"];
                    hollywood.Image = (string)dr["Image"];
                    hollywood.UploadDate = (DateTime)dr["UploadDate"];
                    hollywood.TeamID = (int)dr["TeamID"];
                    hollywood.XUHAO = (int)dr["XUHAO"];

                    list.Add(hollywood);
                }
            }
            return list;
        }
    }
}
